package com.itboy.controller;

import com.alibaba.fastjson.JSON;
import com.itboy.db.DataSourceFactory;
import com.itboy.db.DbSourceFactory;
import com.itboy.db.JdbcUtils;
import com.itboy.db.SqlDruidParser;
import com.itboy.model.*;
import com.itboy.service.DbSourceService;
import lombok.extern.slf4j.Slf4j;
import org.apache.shiro.SecurityUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 操作数据源及执行sql
 */
@Slf4j
@Controller
@RequestMapping(value = "/db")
public class DbSourceController implements Serializable {
    private static final long serialVersionUID = 3942616757578310178L;
    @Resource private DbSourceService dbSourceService;
    @Resource private DbSourceFactory dbSourceFactory;

    @GetMapping("/source")
    public String dbSource() {
        return "dbSource";
    }

    @GetMapping("/sql/record")
    public String sqlRecord() {
        return "sqlRecord";
    }

    @GetMapping("/sql")
    public String sql() {
        return "sql";
    }

    @RequestMapping("/log/logPage")
    public String logPage() {
        return "logPage";
    }

    @ResponseBody
    @RequestMapping("/sqlController/tablePage")
    public String tablePage() {
        //可自行实现
        return "请通过SQL编写查询表";
    }

    @RequestMapping("/sourceController/addSourcePage")
    public String addSourcePage() {
        return "addSourcePage";
    }

    @ResponseBody
    @RequestMapping("/dbsourceList")
    public Map dbsourceList(DbSourceModel model) {
        Result<DbSourceModel> p = dbSourceService.selectDbSourceList(model);
        Map result = new HashMap();
        Map reuslt2 = new HashMap();
        reuslt2.put("totalCount", p.getCount());
        reuslt2.put("pageSize", model.getLimit());
        reuslt2.put("list", p.getData());
        result.put("code", 0);
        result.put("page", reuslt2);
        return result;
    }

    /*测试数据源*/
    @ResponseBody
    @RequestMapping("/testUrl")
    public String testUrl(DbSourceModel model) {
        Connection conn = null;
        Statement pre = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(model.getDburl().trim(), model.getDbaccount().trim(), model.getDbpass().trim());
            pre = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pre.executeQuery(model.getDbTestUrl());
            if (rs.first()) {
                return "连接成功！";
            } else {
                return "连接失败,返回结果集为空！";
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
            return "连接失败！error:" + e.getMessage();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pre != null) pre.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
    }

    /*新增数据源*/
    @ResponseBody
    @PostMapping("/sourceController/addDbSource")
    public String addDbSource(DbSourceModel model) {
        try {
            DataSourceFactory.saveDataSource(model);
            dbSourceService.addDbSource(model);
            log.info("Successful Add  DbSources ");
        } catch (Exception e) {
            return "新增失败！error：" + e.getMessage();
        }
        return "新增成功！";
    }

    /*删除数据源*/
    @ResponseBody
    @RequestMapping("/sourceController/delDbSource/{id}")
    public String delDbSource(@PathVariable  String id) {
        Map<String,Object> result = new HashMap<>();
        result.put("code",0);
        try {
             DbSourceModel model = dbSourceService.delDbSource(id);
             DataSourceFactory.removeDataSource(model.getDbname());
        } catch (Exception e) {
            result.put("code",1);
            result.put("msg","删除失败！error：" + e.getMessage());
        }
        return JSON.toJSONString(result);
    }

    /*加载数据源*/
    @ResponseBody
    @RequestMapping("/dbsourceSqlList")
    public Map dbSourceSqlList(DbSourceModel model) {
        List<DbSourceModel> resultData = dbSourceService.dbSourceSqlList(model);
        Map result = new HashMap();
        result.put("code", 0);
        result.put("data", resultData);
        return result;
    }

    /*执行器*/
    @ResponseBody
    @RequestMapping("/sqlExecute/executeSql")
    public Map executeSql(ExecuteSql sql) {
        Map result = new HashMap();
        SysLog logs = new SysLog();
        SysUser user = (SysUser) SecurityUtils.getSubject().getPrincipal();
        String userName = user.getUserId()+":"+user.getUserName();
        try {
            result.put("code", 1);
            Map<String, Object> sqlParser = SqlDruidParser.sqlParser(sql.getDataType(), sql.getSqlText());
            if (sqlParser.get("executeType") == null) {
                throw new NullPointerException("SQL解析异常");
            }
            result.put("sqlExecuteType", sqlParser.get("executeType"));
            List<String> executeSqlList = (List<String>) sqlParser.get("executeSql");
            List dataList = new ArrayList();
            if (sqlParser.get("executeType").equals("SELECT")) {
                for (String executeSql : executeSqlList
                        ) {
                    Map<String, Object> resultData = JdbcUtils.findMoreResult(sql.getDataType(), executeSql, new ArrayList<>());
                    dataList.add(resultData);
                }
            } else {
                for (String executeSql : executeSqlList
                        ) {
                    Map<String, Object> resultData = JdbcUtils.updateByPreparedStatement(sql.getDataType(), executeSql, new ArrayList<>());
                    dataList.add(resultData);
                }
            }
            //未改造日志记录
            logs.setLogContent(sql.getSqlText());
            logs.setLogResult(dataList.toString());
            logs.setLogType("1");
            logs.setLogName("SQL执行");
            logs.setLogDbSource(sql.getDataType());
            logs.setUserid(userName);
            result.put("dataList", dataList);
        } catch (Exception e) {
            logs.setLogContent(sql.getSqlText());
            logs.setLogResult(e.getMessage());
            logs.setLogType("1");
            logs.setLogName("sql执行记录");
            logs.setLogDbSource(sql.getDataType());
            logs.setUserid(userName);
            log.error(e.getMessage());
            result.put("code", 2);
            result.put("msg", e.getMessage());
        }finally {
            SysConfig sysConfig = dbSourceFactory.getSysConfig();
            if(sysConfig.getCol3()==1){ dbSourceService.insertLog(logs);}
        }
        return result;
    }

    /*暂未实现获取表功能*/
    @ResponseBody
    @RequestMapping("/getTableList")
    public Map<String, Object> getTableList(ExecuteSql sql){
        Map<String, Object> result = new HashMap<>();
        return result;
    }

    /*sql文本列表*/
    @ResponseBody
    @RequestMapping("/getSqlTextList")
    public Map<String, Object> getSqlTextList(DbSqlText model) {
        Result<DbSqlText> p = dbSourceService.getDbSqlText(model);
        Map<String, Object> result = new HashMap<>();
        Map<String, Object> reuslt2 = new HashMap<>();
        reuslt2.put("totalCount", p.getCount());
        reuslt2.put("pageSize", model.getLimit());
        reuslt2.put("list", p.getData());
        result.put("code", 0);
        result.put("page", reuslt2);
        return result;
    }

    /*保存sql文本*/
    @ResponseBody
    @RequestMapping("/sqlController/saveSqlText")
    public String saveSqlText(DbSqlText model) {
        try {
            dbSourceService.saveSqlText(model);
        } catch (Exception e) {
            return "新增失败！error：" + e.getMessage();
        }
        return "新增成功！";
    }

    /*查询sql文本*/
    @ResponseBody
    @RequestMapping("/sqlController/sqlTextList")
    public Map<String, Object> sqlTextList(DbSourceModel model) {
        List<DbSourceModel> resultData = dbSourceService.sqlTextList(model);
        Map<String, Object> result = new HashMap<>();
        result.put("code", 0);
        result.put("data", resultData);
        return result;
    }

    //删除sql
    @ResponseBody
    @RequestMapping("/sqlController/delsqlText/{id}")
    public String delsqlText(@PathVariable  String id) {
        Map<String,Object> result = new HashMap<>();
        result.put("code",0);
        try {
            dbSourceService.delSqlText(id);
        } catch (Exception e) {
            result.put("code",1);
            result.put("msg","删除失败！error：" + e.getMessage());
        }
        return JSON.toJSONString(result);
    }

    @ResponseBody
    @RequestMapping("/getLogList")
    public Map<String, Object> getLogList(SysLog model) {
        Result<SysLog> p = dbSourceService.getLogList(model);
        Map<String, Object> result = new HashMap<>();
        Map<String, Object> reuslt2 = new HashMap<>();
        reuslt2.put("totalCount", p.getCount());
        reuslt2.put("pageSize", model.getLimit());
        reuslt2.put("list", p.getData());
        result.put("code", 0);
        result.put("page", reuslt2);
        return result;
    }

    //删除日志
    @ResponseBody
    @RequestMapping("/sqlController/delLog/{type}")
    public String delLog(@PathVariable int type) {
        Map<String,Object> result = new HashMap<>();
        result.put("code",0);
        try {
            if(type==1){
                dbSourceService.delSysLog();
            }else if(type==2){
                dbSourceService.delUserLog();
            }
        } catch (Exception e) {
            result.put("code",1);
            result.put("msg","删除失败！error：" + e.getMessage());
        }
        return JSON.toJSONString(result);
    }
}